package com.neuedu.corejava.jdbc.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.neuedu.corejava.jdbc.DBHelper;
import com.neuedu.corejava.jdbc.bean.Users;
import com.neuedu.corejava.jdbc.dao.IUserDao;

/**
 * 
 * @author admit
 *
 */
public class UserDaoImpl implements IUserDao {

	@Override
	public boolean add(Users param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement pst = null;

		try {
			// 编写sql时，如果需要传递参数，则使用？进行占位
			String sql = "insert into users(id,name,age,birthdate) values(SEQ_USERS.NEXTVAL,?,?,?)";
			pst = conn.prepareStatement(sql);
			pst.setString(1, param.getName());
			pst.setInt(2, param.getAge());
			// 参数值是java.sql.Date,只能保存年月日;这里一般需要把util.Date转换为sql.Date
			// stmt.setDate(3, new
			// java.sql.Date(param.getBirthDate().getTime()));
			// 参数值是java.sql.Time,只能保存时分秒;这里一般需要把util.Date转换为sql.Time
			// stmt.setTime(3, new
			// java.sql.Time(param.getBirthDate().getTime()));

			// 参数值是java.sql.Timestamp,只能保存年月日时分秒;这里一般需要把util.Date转换为sql.Timestamp
			pst.setTimestamp(3, new java.sql.Timestamp(param.getBirthdate()
					.getTime()));
			int count = pst.executeUpdate();// executeUpdate会返回sql更新数据时更新了多少行，更新行数
			if (count != 0) {
				// 新增成功，返回true --在项目中注释不能写语法，要写业务说明
				return true;// 一个方法内部可有多个return
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, pst, conn);
		}
		return false;
	}

	@Override
	public boolean update(Users param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement pst = null;
		try {
			// 主键和账号不可以修改
			String sql = "update users set age = ?,birthdate = ? where id = ?";
			pst = conn.prepareStatement(sql);
			pst.setInt(1, param.getAge());
			pst.setTimestamp(2, new java.sql.Timestamp(param.getBirthdate()
					.getTime()));
			pst.setInt(3, param.getId());
			int count = pst.executeUpdate();
			if (count != 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, pst, conn);
		}
		return false;
	}

	@Override
	public boolean deleteById(int id) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement pst = null;
		try {
			// 编写sql时，如果需要传递参数，则使用？进行占位
			String sql = "delete from users where id = ?";
			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			int count = pst.executeUpdate();
			if (count != 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, pst, conn);
		}
		return false;
	}

	@Override
	public Users findById(int id) {
		Users users = new Users();
		Connection conn = DBHelper.openConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			// 编写sql时，如果需要传递参数，则使用？进行占位
			String sql = "select id,name,age,birthdate from users where id = ?";
			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			rs = pst.executeQuery();
			if (rs.next()) {
				users.setId(rs.getInt(1));
				users.setName(rs.getString("name"));
				users.setAge(rs.getInt("age"));
				users.setBirthdate(rs.getTimestamp("birthdate"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, pst, conn);
		}
		return users;
	}

	@Override
	public List<Users> findAll() {

		List<Users> list = new ArrayList<>();
		Connection conn = DBHelper.openConnection();
		// Statement stm = null; 可能造成sql注入攻击
		// 可以防止sql注入攻击，在批量更新时具有更好的性能
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			// 3.打开状态连接
			String sql = "select id,name,age,birthdate from users";
			pst = conn.prepareStatement(sql);
			// 4.执行sql
			rs = pst.executeQuery();
			// 5.处理sql结果
			while (rs.next()) {// next()方法从结果集中取出一行数据，如果有数据则返回true，否则返回false
				// 可以根据列的序号获取列的数据，列的序号从1开始，1表示第一列
				int id = rs.getInt(1);
				// 可以根据列名或列的别名获取列的数据
				String name = rs.getString("name");
				int age = rs.getInt("age");

				// getTime,返回java.lang.Time，sql.Time是java.util.Date的子类，可以保存时分秒
				// getDate,返回java.lang.Date，sql.Date是java.util.Date的子类，可以保存年月日
				// getTimestamp返回java.sql.Timestamp,Timestamp是java.util.Date的子类，可以保存年月日时分秒毫秒
				Date birthdate = rs.getTimestamp("birthdate");

				Users users = new Users(id, name, age, birthdate);
				list.add(users);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 关闭连接，释放资源
			DBHelper.closeAll(rs, pst, conn);
		}

		return list;
	}

	@Override
	public List<Users> findByExample(Users param) {

		List<Users> list = new ArrayList<>();
		Connection conn = DBHelper.openConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			// 编写sql时，如果需要传递参数，则使用？进行占位
			String sql = "select id,name,age,birthdate from users where name like ?";
			pst = conn.prepareStatement(sql);
			pst.setString(1, "%" + param.getName() + "%");
			rs = pst.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString("name");
				int age = rs.getInt("age");
				Date birthdate = rs.getTimestamp("birthdate");
				Users users = new Users(id, name, age, birthdate);
				list.add(users);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, pst, conn);
		}
		return list;
	}

	@Override
	public List<Users> findByPager(int page, int size) {

		List<Users> list = new ArrayList<>();
		Connection conn = DBHelper.openConnection();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			// 编写sql时，如果需要传递参数，则使用？进行占位
			String sql = "select * from "
					+ "(select rownum rn,id,name,age,birthdate from users where rownum <= ?) "
					+ "where rn > ?";

			pst = conn.prepareStatement(sql);
			pst.setInt(1, page * size);
			pst.setInt(2, (page - 1) * size);
			rs = pst.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString("name");
				int age = rs.getInt("age");
				Date birthdate = rs.getTimestamp("birthdate");
				Users users = new Users(id, name, age, birthdate);
				list.add(users);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, pst, conn);
		}
		return list;
	}

	@Override
	public int deleteByIds(int[] ids) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement pst = null;
		
		int result = 0;
		//业务说明：批量删除数据，只要有一条删除失败，则所有都不删除
		try {
			//关闭自动提交功能，就是打开事务
			conn.setAutoCommit(false);
			String sql = "delete from users where id = ?";
			pst = conn.prepareStatement(sql);
			for (int id : ids) {
				pst.setInt(1, id);
				//批量发送，每循环一次就是一批数据
				pst.addBatch();
			}
			//返回一个数组，保存了每批数据更新的情况，每批数据更新的数量（0或1）
			int[] counts = pst.executeBatch();
			for (int i : counts) { //统计有多少批数据删除成功
				if(i == 1){
					result++;
				}
			}
			if (result == ids.length) {
				conn.commit();
				conn.setAutoCommit(true);
			}else {
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			if(conn!=null){//删除数据，如有数据删除异常，则需要全部回滚
				try {
					conn.rollback();
					conn.setAutoCommit(true);
				} catch (SQLException e1) {
				}
			}
		} finally {
			DBHelper.closeAll(null, pst, conn);
		}
		return result;
	}

	@Override
	public int addAll(List<Users> param) {
		Connection conn = DBHelper.openConnection();
		PreparedStatement pst = null;		
		int result = 0;
		//业务说明：批量添加数据，只要有一条添加失败，则所有都不添加
		try {
			//关闭自动提交功能，就是打开事务
			conn.setAutoCommit(false);
			String sql = "insert into users(id,name,age,birthdate) values(SEQ_USERS.NEXTVAL,?,?,?)";
			pst = conn.prepareStatement(sql);
			for (Users users : param) {
				pst.setString(1, users.getName());
				pst.setInt(2, users.getAge());		
				pst.setTimestamp(3, new java.sql.Timestamp(users.getBirthdate()
						.getTime()));
				pst.addBatch();
			}
			
			//返回一个数组，保存了每批数据更新的情况，每批数据更新的数量（0或1）
			int[] counts = pst.executeBatch();
			for (int i : counts) { //统计有多少批数据添加成功
				System.out.println(i);
				if(i == 1){
					result++;
				}
			}
			if (result == param.size()) {
				conn.commit();
				conn.setAutoCommit(true);
			}else {
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			if(conn!=null){//删除数据，如有数据删除异常，则需要全部回滚
				try {
					conn.rollback();
					conn.setAutoCommit(true);
				} catch (SQLException e1) {
				}
			}
		} finally {
			DBHelper.closeAll(null, pst, conn);
		}
		return result;
	}

	@Override
	public int editAll(List<Users> param) {
		
		Connection conn = DBHelper.openConnection();
		PreparedStatement pst = null;		
		int result = 0;
		//业务说明：批量修改数据，只要有一条修改失败，则所有都不修改
		try {
			//关闭自动提交功能，就是打开事务
			conn.setAutoCommit(false);
			String sql = "update users set age = ?,birthdate = ? where id = ?";
			pst = conn.prepareStatement(sql);
			for (Users users : param) {
				pst.setInt(1, users.getAge());
				pst.setTimestamp(2, new java.sql.Timestamp(users.getBirthdate()
						.getTime()));
				pst.setInt(3, users.getId());
				pst.addBatch();
			}
			//返回一个数组，保存了每批数据更新的情况，每批数据更新的数量（0或1）
			int[] counts = pst.executeBatch();
			for (int i : counts) { //统计有多少批数据修改成功
				if(i == 1){
					result++;
				}
			}
			if (result == param.size()) {
				conn.commit();
				conn.setAutoCommit(true);
			}else {
				conn.rollback();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			if(conn!=null){//修改数据，如有数据修改异常，则需要全部回滚
				try {
					conn.rollback();
					conn.setAutoCommit(true);
				} catch (SQLException e1) {
				}
			}
		} finally {
			DBHelper.closeAll(null, pst, conn);
		}
		return result;
	}
}
